package com.tocea.formation.books;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tocea.formation.connection.ConnectionManager;
import com.tocea.formation.users.UserImpl;


public class BookDAO {
	
	public BookDAO(){
		ConnectionManager.getConnectionManager();
	}
	
	public static Integer add(IBook book){
		String author = book.getAuthor();
		Integer exemplaire = book.getExemplaire();
		String name = book.getName();
		String query = "INSERT INTO books (author, name, exemplaire) VALUES ('"+author+"','"+name+"','"+exemplaire+"')";
		return ConnectionManager.executeUpdate(query);
	}
	
	public static Integer update(IBook book){
		String author = book.getAuthor();
		Integer exemplaire = book.getExemplaire();
		String name = book.getName();
		Integer id = book.getId();
		String query = "REPLACE INTO books (id, author, name, exemplaire) VALUES ('"+id+"','"+author+"','"+name+"','"+exemplaire+"')";
		return ConnectionManager.executeUpdate(query);
	}
	
	public static BookImpl get(Integer id){
		String query = "SELECT * FROM books where id = '"+id.toString()+"'";
		ResultSet res = ConnectionManager.executeQuery(query);
		System.out.println(query);
		try {
			res.next();
			if(res.getRow() > 0){
				while(res.first()){
					return new BookImpl(id, res.getString("author"), res.getString("name"), (Integer)res.getInt("exemplaire"));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	

	public static IBook getBookByRent(Integer rentID){
		String query = "select b.id id, b.author author, b.name name, b.exemplaire exemplaire from  books b JOIN rent r ON r.idBook = b.id WHERE r.id =  '"+rentID.toString()+"'";
		ResultSet res = ConnectionManager.executeQuery(query);
		System.out.println(query);
		try {
			res.next();
			if(res.getRow() > 0){
				while(res.first()){
					return new BookImpl((Integer)res.getInt("id"), res.getString("author"), res.getString("name"), (Integer)res.getInt("exemplaire"));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public static List<BookImpl> getAll(){
		List<BookImpl> books = new ArrayList<BookImpl>(); 
		String query = "Select  * from books";
		System.out.println(query);
		ResultSet result = ConnectionManager.executeQuery(query);
		try{
			while(result.next()){
				books.add( new BookImpl(result.getInt("id"), result.getString("author"), result.getString("name"), (Integer)result.getInt("exemplaire")));
			}
			return books;
		}catch (SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static Integer nbBooks(){
		String query = "select count(*) nbresult from books";
		System.out.println(query);
		ResultSet result = ConnectionManager.executeQuery(query);
		try{
			result.next();
			return (Integer)result.getInt("nbresult");
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
}
